<?php 
  require_once("../includes/config.php"); 
  $length = 20;
  $creditor = 0;
  $debitor = 0;
  $paid = 0;

  if( isset($_POST['page'])  && !empty($_POST['page'] ))
  {
    $page =  filter_var($_POST['page'], FILTER_SANITIZE_NUMBER_INT);
    $start = $page*20 - 20;
  }
  else
  {
    $start = 0;
    $page = 1;
  }
  
  $sql = 'SELECT * FROM contracts 
  LEFT JOIN clients ON 
    contracts.client_id=clients.id 
  LEFT JOIN companies ON 
    contracts.company_id=companies.company_id 
  LEFT JOIN apartments ON
  contracts.apartment_name=apartments.apart_name
  WHERE contracts.owner_id = ? ';
  
  $sqlCount = 'SELECT COUNT(*) FROM contracts 
  LEFT JOIN clients ON 
    contracts.client_id=clients.id 
  LEFT JOIN companies ON 
    contracts.company_id=companies.company_id 
	 LEFT JOIN apartments ON
  contracts.apartment_name=apartments.apart_name
  WHERE contracts.owner_id = ?';
  
  $binds = array();
  
  $binds['owner_id'] = $_SESSION['user_id'];
                  
  if(!empty($_POST['contract_number']) )
  {
    $binds['contract_number'] = $_POST['contract_number'];
    $sql .= " and contracts.contract_id = ?";
    $sqlCount .= " and contracts.contract_id = ?";
  }
        
  if(!empty($_POST['client_name']))
  {
    $binds['client_name'] = "%".$_POST['client_name']."%";
    $sql .= " and name LIKE ?";
    $sqlCount .= " and name LIKE ?";
  }
        
  if(!empty($_POST['mobile']))
  {
    $binds['mobile'] = $_POST['mobile'];
    $sql .= " and mobile = ? ";
    $sqlCount .= " and mobile = ? ";
  }
  
  if(!empty($_POST['from']) && !empty($_POST['to']))
  {
    $binds['from'] = $_POST['from'];
    $binds['to'] = $_POST['to'];
    $sql .= " and mildai_from between ? AND ?";
    $sqlCount .= " and mildai_from between ? AND ?";
  }
        
  if(!empty($_POST['floor']))
  {
    $binds['floor'] = $_POST['floor'];
    $sql .= " and floor  = ? ";
    $sqlCount .= " and floor  = ? ";
  }
        
  if(!empty($_POST['apart_num']))
  {
    $binds['apart_num'] = $_POST['apart_num'];
    
    $sql .= " and apart_name = ? ";
    $sqlCount .= " and apart_name = ? ";
  }
        
  if(!empty($_POST['company_name']))
  {
    $binds['company_name'] = $_POST['company_name'];
    $sql .= " and MATCH (company_name) AGAINST (? IN BOOLEAN MODE)";
    $sqlCount .= " and MATCH (company_name) AGAINST (? IN BOOLEAN MODE)";
  }
        
  if(isset($_POST['rent_type']) && $_POST['rent_type'] != -1)
  {
    $binds['rent_type'] = $_POST['rent_type'];
    $sql .= " and rent_type = ?";
    $sqlCount .= " and rent_type = ?";
  }
  
  if(isset($_POST['contract_status']))
  {
    if($_POST['contract_status'] == 1 )
    {
      //calculate paid
      $paid = query("SELECT SUM(paid) FROM contracts where owner_id = ? and (contract_status = 1 or contract_status = 2)",$_SESSION['user_id']);
      if(!empty($paid))
        $paid = $paid[0]['SUM(paid)'];
        
      //calculate debit
      $rows = query("SELECT total,paid,paid_back,services_revenue FROM contracts where owner_id = ? and (paid-paid_back-total-services_revenue)>0",$_SESSION['user_id']);
      if(!empty($rows))
      {
        $creditor = 0;
        foreach($rows as $row)
          $creditor += ($row['paid']-$row['paid_back']-$row['total']-$row['services_revenue']);
      }
      
      //calculate credit
      $rows = query("SELECT total,paid,paid_back,services_revenue FROM contracts where owner_id = ? and (paid-paid_back-total-services_revenue)<0",$_SESSION['user_id']);
      if(!empty($rows))
      {
        $debitor = 0;
        foreach($rows as $row)
          $debitor += ($row['paid']-$row['paid_back']-$row['total']-$row['services_revenue']);
      }
      
      $binds['contract_status'] = 1;
      $binds['contract_status2'] = 2;
      $sql .= " and (contract_status = ? or  contract_status = ?)";
      $sqlCount .= " and (contract_status = ? or  contract_status = ?)";
    }
    else
    {
      //calculate paid
      $paid = query("SELECT SUM(paid) FROM contracts where owner_id = ? and (contract_status = 3)",$_SESSION['user_id']);
      if(!empty($paid))
        $paid = $paid[0]['SUM(paid)'];
        
      $binds['contract_status'] = $_POST['contract_status'];
      $sql .= " and contract_status = ?";
      $sqlCount .= " and contract_status = ?";
    }
    
  }
  //default case
  else
  {   
      //calculate paid
      $paid = query("SELECT SUM(paid) FROM contracts where owner_id = ? and (contract_status = 1 or contract_status = 2)",$_SESSION['user_id']);
      if(!empty($paid))
        $paid = $paid[0]['SUM(paid)'];
        
      $rows = query("SELECT total,paid,paid_back,services_revenue FROM contracts where owner_id = ? and (paid-paid_back-total-services_revenue)>0",$_SESSION['user_id']);
      if(!empty($rows))
      {
        $creditor = 0;
        foreach($rows as $row)
          $creditor += ($row['paid']-$row['paid_back']-$row['total']-$row['services_revenue']);
       
      }
      //calculate credit
      $rows = query("SELECT total,paid,paid_back,services_revenue FROM contracts where owner_id = ? and (paid-paid_back-total-services_revenue)<0",$_SESSION['user_id']);
      if(!empty($rows))
      {
        $debitor = 0;
        foreach($rows as $row)
          $debitor += ($row['paid']-$row['paid_back']-$row['total']-$row['services_revenue']);
      }
        
      $binds['contract_status'] = 1;
      $binds['contract_status2'] = 2;
      $sql .= " and (contract_status = ? or  contract_status = ?)";
      $sqlCount .= " and (contract_status = ? or  contract_status = ?)";
  }
  
  
  $sql .= "  ORDER BY contracts.updated_at DESC LIMIT ?,?";
  $binds['start'] = $start;
  $binds['end'] = $length;
  
  
  $contracts = query($sql,$binds);
  $Count = query($sqlCount,$binds);
  $totalPages = $Count[0]['COUNT(*)'];
  
    if(!empty($_POST['client_name']))
		$binds['client_name'] = $_POST['client_name'];

  render("bookings.php", array("title"=>"إدارة التأجير و الحجوزات","contracts"=>$contracts,"body_class"=>"booking","totalPages"=>$totalPages,"binds"=>$binds,"page"=>$page,"paid_statistics"=>$paid,"creditor"=>$creditor,"debitor"=>$debitor,"payment_method"=>$payment_method));

?>
